package com.cait.dutyfree.dataaccess.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.cait.dutyfree.dataaccess.InventoryServiceDAO;
import com.cait.dutyfree.dataaccess.ProductServiceDAO;
import com.cait.dutyfree.pojo.AirlineProduct;
import com.cait.dutyfree.pojo.Product;
import com.cait.dutyfree.pojo.Stock;
import com.cait.dutyfree.pojo.view.ProductDTO;

public class ProductServiceDAOImpl extends HibernateDaoSupport implements
		ProductServiceDAO {
	private InventoryServiceDAO inventoryServiceDAO;

	// spring injection starts here
	public void setInventoryServiceDAO(InventoryServiceDAO inventoryServiceDAO) {
		this.inventoryServiceDAO = inventoryServiceDAO;
	}

	// spring injection ends here

	public List<Product> findproducts() {
		try {
			ArrayList<Product> products = new ArrayList<Product>();
			DetachedCriteria criteria = DetachedCriteria
					.forClass(Product.class);
			@SuppressWarnings("rawtypes")
			List prdlist = getHibernateTemplate().findByCriteria(criteria);

			if ((prdlist != null) && (prdlist.size() > 0)) {
				for (int i = 0; i < prdlist.size(); i++) {
					products.add((Product) prdlist.get(i));
				}
				return products;
			}
			return null;
		} catch (Exception e) {
			logger.warn("During fetch all products ");
			return null;

		}
	}

	public List<Product> findProductsByArilineId(int airlineId) {
		@SuppressWarnings("unchecked")
		List<Product> products = getHibernateTemplate()
				.find("select distinct p from Product p join p.airlineProducts ad where ad.airline.id = ?",
						airlineId);
		return products;
	}

	public Product getById(int id) {
		try {
			DetachedCriteria criteria = DetachedCriteria
					.forClass(Product.class);
			criteria.add(Restrictions.eq("id", id));
			@SuppressWarnings("rawtypes")
			List products = getHibernateTemplate().findByCriteria(criteria);
			Product p;
			if ((products != null) && (products.size() > 0)) {
				p = (Product) products.get(0);
				return p;
			}
			return null;
		} catch (Exception e) {
			logger.warn("During fetch user by username ");
			return null;
		}
	}

	public boolean isProductAvailableForAirline(int airlineId, int productId) {
		final int i = airlineId;
		final int j = productId;
		try {
			List l = (List) getHibernateTemplate().execute(
					new HibernateCallback() {
						@Override
						public Object doInHibernate(Session arg0)
								throws HibernateException, SQLException {
							SQLQuery query = arg0
									.createSQLQuery("select * from airline_product where airline_id = ? and product_id = ?");
							query.setInteger(0, i);
							query.setInteger(1, j);
							List l = query.list();
							return l;
						}
					});
			if (l.size() > 0) {
				for (int k = 0; k < l.size(); k++) {
					System.out.println(l.get(k)
							+ "hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh");
				}
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.warn("&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&");
			return false;
		}
	}

	@Override
	public List<ProductDTO> findProductsDTOByArilineId(int airlineId) {
		List<Product> products = this.findProductsByArilineId(airlineId);
		List<ProductDTO> productsDto = new ArrayList<ProductDTO>();
		for (int i = 0; i < products.size(); i++) {
			ProductDTO pdto = new ProductDTO();
			Product p = products.get(i);

			// set productdto properties
			pdto.setId(p.getId());
			Stock stock = null;
			try {
				stock = this.inventoryServiceDAO.getStock(p);
				pdto.setAvailableCount(stock.getQuantity());
			} catch (Exception e) {
				e.printStackTrace();
				pdto.setAvailableCount(0);
			}
			List<AirlineProduct> airlineDetails = p.getAirlineProducts();
			for (int j = 0; j < airlineDetails.size(); j++) {
				AirlineProduct ad = airlineDetails.get(j);
				if (ad.getAirline().getId() == airlineId) {
					pdto.setDiscount(ad.getDiscountRate());
					pdto.setPrice(ad.getUnitPrice());
					pdto.setCurrencyCode(ad.getCurrency().getCode());
				}
			}
			pdto.setPrdImageUrl1(p.getPrdImageUrl1());
			pdto.setPrdImageUrl2(p.getPrdImageUrl2());
			pdto.setPrdImageUrl3(p.getPrdImageUrl3());
			pdto.setProductCode(p.getCode());
			pdto.setProductDescription(p.getDescription());
			pdto.setProductName(p.getName());
			pdto.setVendor(p.getVendor());
			pdto.setCategoryName(p.getCategory().getName());
			pdto.setSubCategoryName(p.getSubCategory().getName());
			// end properties

			if (pdto.getAvailableCount() > 0) {
				productsDto.add(pdto);
			}
		}
		return productsDto;
	}

	@Override
	public List<ProductDTO> findProductsDTOs() {
		List<Product> products = this.findproducts();
		List<ProductDTO> productsDto = new ArrayList<ProductDTO>();
		for (int i = 0; i < products.size(); i++) {
			ProductDTO pdto = new ProductDTO();
			Product p = products.get(i);

			// set productdto properties
			pdto.setId(p.getId());
			Stock stock = null;
			try {
				stock = this.inventoryServiceDAO.getStock(p);
				pdto.setAvailableCount(stock.getQuantity());
			} catch (Exception e) {
				e.printStackTrace();
				pdto.setAvailableCount(0);
			}
			List<AirlineProduct> airlineDetails = p.getAirlineProducts();
			if (airlineDetails.size() > 0) {
				AirlineProduct ad = airlineDetails.get(0);
				pdto.setDiscount(ad.getDiscountRate());
				pdto.setPrice(ad.getUnitPrice());
				pdto.setCurrencyCode(ad.getCurrency().getCode());
			}

			pdto.setPrdImageUrl1(p.getPrdImageUrl1());
			pdto.setPrdImageUrl2(p.getPrdImageUrl2());
			pdto.setPrdImageUrl3(p.getPrdImageUrl3());
			pdto.setProductCode(p.getCode());
			pdto.setProductDescription(p.getDescription());
			pdto.setProductName(p.getName());
			pdto.setVendor(p.getVendor());
			pdto.setCategoryName(p.getCategory().getName());
			pdto.setSubCategoryName(p.getSubCategory().getName());
			// end properties

			productsDto.add(pdto);
		}
		return productsDto;
	}

	public ProductDTO findProductDTOByProductIdAndAirlineId(Integer productId,
			Integer airlineId) {
		Product p = this.getById(productId);
		ProductDTO pdto = new ProductDTO();
		pdto.setId(p.getId());
		Stock stock = null;
		try {
			stock = this.inventoryServiceDAO.getStock(p);
			pdto.setAvailableCount(stock.getQuantity());
		} catch (Exception e) {
			e.printStackTrace();
			pdto.setAvailableCount(0);
		}
		List<AirlineProduct> airlineDetails = p.getAirlineProducts();
		for (int j = 0; j < airlineDetails.size(); j++) {
			AirlineProduct ad = airlineDetails.get(j);
			if (ad.getAirline().getId() == airlineId) {
				pdto.setDiscount(ad.getDiscountRate());
				pdto.setPrice(ad.getUnitPrice());
				pdto.setCurrencyCode(ad.getCurrency().getCode());
			}
		}
		pdto.setPrdImageUrl1(p.getPrdImageUrl1());
		pdto.setPrdImageUrl2(p.getPrdImageUrl2());
		pdto.setPrdImageUrl3(p.getPrdImageUrl3());
		pdto.setProductCode(p.getCode());
		pdto.setProductDescription(p.getDescription());
		pdto.setProductName(p.getName());
		pdto.setVendor(p.getVendor());
		pdto.setCategoryName(p.getCategory().getName());
		pdto.setSubCategoryName(p.getSubCategory().getName());

		return pdto;
	}

}
